package pg

import (
	"testing"

	"github.com/stretchr/testify/require"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/schema"
	"github.com/bytebase/bytebase/backend/store/model"
)

// TestCommentOnTable tests COMMENT ON TABLE parsing
func TestCommentOnTable(t *testing.T) {
	// User pasted schema
	userSchema := `
		CREATE TABLE "public"."invoices" (
			"id" integer NOT NULL,
			"total" numeric
		);

		COMMENT ON TABLE "public"."invoices" IS 'Invoice records';
	`

	// Database current state
	dbSchema := `
		CREATE TABLE invoices (
			id INT NOT NULL,
			total NUMERIC
		);

		COMMENT ON TABLE invoices IS 'Invoice records';
	`

	userMetadata, err := GetDatabaseMetadata(userSchema)
	require.NoError(t, err)
	require.NotNil(t, userMetadata)

	dbMetadata, err := GetDatabaseMetadata(dbSchema)
	require.NoError(t, err)
	require.NotNil(t, dbMetadata)

	// Verify table comments are parsed
	require.NotEmpty(t, userMetadata.Schemas)
	require.NotEmpty(t, userMetadata.Schemas[0].Tables)
	require.Equal(t, "Invoice records", userMetadata.Schemas[0].Tables[0].Comment)

	require.NotEmpty(t, dbMetadata.Schemas)
	require.NotEmpty(t, dbMetadata.Schemas[0].Tables)
	require.Equal(t, "Invoice records", dbMetadata.Schemas[0].Tables[0].Comment)

	// Compare schemas
	userDBSchema := model.NewDatabaseMetadata(userMetadata, nil, nil, storepb.Engine_POSTGRES, false)
	dbDBSchema := model.NewDatabaseMetadata(dbMetadata, nil, nil, storepb.Engine_POSTGRES, false)

	diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_POSTGRES, dbDBSchema, userDBSchema)
	require.NoError(t, err)

	migration, err := generateMigration(diff)
	require.NoError(t, err)

	// Should not generate COMMENT statements when comments are identical
	require.NotContains(t, migration, "COMMENT ON TABLE", "Should not generate COMMENT statements for unchanged comments")
}

// TestCommentOnColumn tests COMMENT ON COLUMN parsing including Chinese characters
func TestCommentOnColumn(t *testing.T) {
	// User pasted schema with column comments
	userSchema := `
		CREATE TABLE "public"."orders" (
			"id" integer NOT NULL,
			"old_plan_id" text,
			"new_plan_id" text
		);

		ALTER TABLE "public"."orders" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
			SEQUENCE NAME "public"."orders_id_seq"
			START WITH 1
			INCREMENT BY 1
		);

		COMMENT ON COLUMN "public"."orders"."old_plan_id" IS 'Previous plan identifier';
		COMMENT ON COLUMN "public"."orders"."new_plan_id" IS 'Current plan identifier';
	`

	// Database current state with same comments
	dbSchema := `
		CREATE TABLE orders (
			id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			old_plan_id TEXT,
			new_plan_id TEXT
		);

		COMMENT ON COLUMN orders.old_plan_id IS 'Previous plan identifier';
		COMMENT ON COLUMN orders.new_plan_id IS 'Current plan identifier';
	`

	userMetadata, err := GetDatabaseMetadata(userSchema)
	require.NoError(t, err)

	dbMetadata, err := GetDatabaseMetadata(dbSchema)
	require.NoError(t, err)

	// Verify column comments are parsed
	userTable := userMetadata.Schemas[0].Tables[0]
	require.Len(t, userTable.Columns, 3)
	require.Equal(t, "", userTable.Columns[0].Comment)
	require.Equal(t, "Previous plan identifier", userTable.Columns[1].Comment)
	require.Equal(t, "Current plan identifier", userTable.Columns[2].Comment)

	dbTable := dbMetadata.Schemas[0].Tables[0]
	require.Len(t, dbTable.Columns, 3)
	require.Equal(t, "", dbTable.Columns[0].Comment)
	require.Equal(t, "Previous plan identifier", dbTable.Columns[1].Comment)
	require.Equal(t, "Current plan identifier", dbTable.Columns[2].Comment)

	// Compare schemas
	userDBSchema := model.NewDatabaseMetadata(userMetadata, nil, nil, storepb.Engine_POSTGRES, false)
	dbDBSchema := model.NewDatabaseMetadata(dbMetadata, nil, nil, storepb.Engine_POSTGRES, false)

	diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_POSTGRES, dbDBSchema, userDBSchema)
	require.NoError(t, err)

	migration, err := generateMigration(diff)
	require.NoError(t, err)

	// Should not generate COMMENT statements when comments are identical
	require.NotContains(t, migration, "COMMENT ON COLUMN", "Should not generate COMMENT statements for unchanged comments")
}

// TestMultilineComment tests multiline comment parsing
func TestMultilineComment(t *testing.T) {
	userSchema := `
		CREATE TABLE "public"."transactions" (
			"source_type" text
		);

		COMMENT ON COLUMN "public"."transactions"."source_type" IS 'Transaction source types:
0: Mobile app
1: Web portal
2: API integration
3: Partner system
4: Admin panel';
	`

	dbSchema := `
		CREATE TABLE transactions (
			source_type TEXT
		);

		COMMENT ON COLUMN transactions.source_type IS 'Transaction source types:
0: Mobile app
1: Web portal
2: API integration
3: Partner system
4: Admin panel';
	`

	userMetadata, err := GetDatabaseMetadata(userSchema)
	require.NoError(t, err)

	dbMetadata, err := GetDatabaseMetadata(dbSchema)
	require.NoError(t, err)

	// Verify multiline comments are preserved
	userCol := userMetadata.Schemas[0].Tables[0].Columns[0]
	dbCol := dbMetadata.Schemas[0].Tables[0].Columns[0]

	require.Contains(t, userCol.Comment, "\n", "Should preserve newlines")
	require.Equal(t, userCol.Comment, dbCol.Comment, "Comments should be identical")

	// Compare schemas
	userDBSchema := model.NewDatabaseMetadata(userMetadata, nil, nil, storepb.Engine_POSTGRES, false)
	dbDBSchema := model.NewDatabaseMetadata(dbMetadata, nil, nil, storepb.Engine_POSTGRES, false)

	diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_POSTGRES, dbDBSchema, userDBSchema)
	require.NoError(t, err)

	migration, err := generateMigration(diff)
	require.NoError(t, err)

	// Should not generate COMMENT statements
	require.Empty(t, migration, "Should not generate migration for identical comments")
}

// TestAllCommentTypes tests all supported comment types
func TestAllCommentTypes(t *testing.T) {
	schema := `
		CREATE SCHEMA app;
		COMMENT ON SCHEMA app IS 'Application schema';

		CREATE TABLE app.users (
			id INT,
			username TEXT
		);
		COMMENT ON TABLE app.users IS 'User accounts';
		COMMENT ON COLUMN app.users.id IS 'User identifier';
		COMMENT ON COLUMN app.users.username IS 'Login username';

		CREATE INDEX idx_users_username ON app.users(username);
		COMMENT ON INDEX app.idx_users_username IS 'Index for username lookups';

		CREATE VIEW app.active_users AS SELECT * FROM app.users;
		COMMENT ON VIEW app.active_users IS 'Currently active users';

		CREATE SEQUENCE app.user_id_seq;
		COMMENT ON SEQUENCE app.user_id_seq IS 'User ID sequence generator';

		CREATE TYPE app.user_status AS ENUM ('active', 'inactive', 'suspended');
		COMMENT ON TYPE app.user_status IS 'User account status';

		CREATE FUNCTION app.get_user(user_id INT) RETURNS TEXT AS $$
		BEGIN
			RETURN 'user';
		END;
		$$ LANGUAGE plpgsql;
		COMMENT ON FUNCTION app.get_user(INT) IS 'Retrieve user information';
	`

	metadata, err := GetDatabaseMetadata(schema)
	require.NoError(t, err)
	require.NotNil(t, metadata)

	// Find the app schema
	var appSchema *storepb.SchemaMetadata
	for _, s := range metadata.Schemas {
		if s.Name == "app" {
			appSchema = s
			break
		}
	}
	require.NotNil(t, appSchema, "Should find app schema")

	// Verify all comment types
	require.Equal(t, "Application schema", appSchema.Comment)
	require.NotEmpty(t, appSchema.Tables)
	require.Equal(t, "User accounts", appSchema.Tables[0].Comment)
	require.NotEmpty(t, appSchema.Tables[0].Columns)
	require.Equal(t, "User identifier", appSchema.Tables[0].Columns[0].Comment)
	require.NotEmpty(t, appSchema.Tables[0].Indexes)
	require.Equal(t, "Index for username lookups", appSchema.Tables[0].Indexes[0].Comment)
	require.NotEmpty(t, appSchema.Views)
	require.Equal(t, "Currently active users", appSchema.Views[0].Comment)
	require.NotEmpty(t, appSchema.Sequences)
	require.Equal(t, "User ID sequence generator", appSchema.Sequences[0].Comment)
	require.NotEmpty(t, appSchema.EnumTypes)
	require.Equal(t, "User account status", appSchema.EnumTypes[0].Comment)
	require.NotEmpty(t, appSchema.Functions)
	require.Equal(t, "Retrieve user information", appSchema.Functions[0].Comment)
}

// TestCommentEquivalence tests that identical comments don't generate migrations
func TestCommentEquivalence(t *testing.T) {
	sourceSchema := `
		CREATE TABLE products (
			id INT,
			name TEXT
		);
		COMMENT ON TABLE products IS 'Product catalog';
		COMMENT ON COLUMN products.id IS 'Product ID';

		CREATE INDEX idx_name ON products(name);
		COMMENT ON INDEX idx_name IS 'Product name index';

		CREATE SEQUENCE product_seq;
		COMMENT ON SEQUENCE product_seq IS 'Product sequence';
	`

	targetSchema := `
		CREATE TABLE products (
			id INT,
			name TEXT
		);
		COMMENT ON TABLE products IS 'Product catalog';
		COMMENT ON COLUMN products.id IS 'Product ID';

		CREATE INDEX idx_name ON products(name);
		COMMENT ON INDEX idx_name IS 'Product name index';

		CREATE SEQUENCE product_seq;
		COMMENT ON SEQUENCE product_seq IS 'Product sequence';
	`

	sourceMetadata, err := GetDatabaseMetadata(sourceSchema)
	require.NoError(t, err)

	targetMetadata, err := GetDatabaseMetadata(targetSchema)
	require.NoError(t, err)

	sourceDBSchema := model.NewDatabaseMetadata(sourceMetadata, nil, nil, storepb.Engine_POSTGRES, false)
	targetDBSchema := model.NewDatabaseMetadata(targetMetadata, nil, nil, storepb.Engine_POSTGRES, false)

	diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_POSTGRES, sourceDBSchema, targetDBSchema)
	require.NoError(t, err)

	migration, err := generateMigration(diff)
	require.NoError(t, err)

	// Key assertion: no COMMENT statements should be generated
	require.NotContains(t, migration, "COMMENT ON", "Should not generate COMMENT statements for identical comments")
}
